﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace ScrumProject.Models
{
    public class PostRepository : IDisposable
    {
        SMRPO6Context db = new SMRPO6Context();

        public void AddPost(Post post)
        {
            using (var db = new SMRPO6Context())
            {
                db.Database.ExecuteSqlCommand("exec dbo.AddPost  @Content, @Time, @ProjectId, @UserId",
                                              new SqlParameter("Content", post.Content),
                                              new SqlParameter("Time", post.Time),
                                              new SqlParameter("ProjectId", post.ProjectId),
                                              new SqlParameter("UserId", post.UserId));
                db.SaveChanges();

            }

        }

        
      
        public IQueryable<Post> GetAllPosts()
        {
            return db.Posts;
        }

        public IQueryable<Post> GetPostByUser(int id)
        {
            return from Post in db.Posts
                   join User in db.Users on Post.UserId equals User.Id
                   where Post.UserId == id
                   select Post;
        }

        //public string GetUsername(int id)
        //{
        //    return (from Post in db.Posts
        //            join User in db.Users on Post.UserId equals User.Id
        //            where Post.UserId == id
        //            select User.Username).SingleOrDefault();
        //}

        public String GetUserWhoWrotePost(int id)
        {

            return (from Post in db.Posts
                    join User in db.Users on Post.UserId equals User.Id
                    where Post.UserId == id
                    select User.Username).FirstOrDefault();

        }

        public IQueryable<Post> GetAllPostsByProject(int id)
        {

            return from Post in db.Posts
                   where Post.ProjectId == id 
                   select Post;
        }

        public Post getPost(int id)
        {
            return (from Post in db.Posts
                    where Post.Id == id
                    select Post).SingleOrDefault();
        }
       
        public IQueryable<Comment> GetPostsComments(int id)
        {
            return from Comment in db.Comments
                   where Comment.PostId == id
                   select Comment;
        }

        public void DeletePost(Post post)
        {

            using (var db = new SMRPO6Context())
            {
                CommentRepository cr = new CommentRepository();
                cr.DeleteAllCommentsForPost(post.Id);

                if (GetPostsComments(post.Id) == null)
                {
                    db.Posts.Remove(post);
                    db.SaveChanges();
                }


            }

        }

        public void Save()
        {
            db.SaveChanges();
        }

        private bool disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    db.Dispose();
                }
            }
            this.disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}